from datetime import datetime
from typing import Optional, List, Dict, Any, Tuple
from apps.models.models import Student, User, User_Profiles
from apps.schemas.schemas import StudentCreateRequest, StudentUpdateRequest
from tortoise.transactions import in_transaction


async def get_all_students(
        username: Optional[str] = None,
        page: int = 1,
        page_size: int = 10
) -> Tuple[int, List[Dict[str, Any]]]:
    """
    获取所有学生列表（分页）
    :param username: 用户名筛选条件
    :param page: 页码
    :param page_size: 每页数量
    :return: (总数, 学生列表)
    """
    # 构建基础查询
    query = Student.all().select_related('user_id')
    # 如果提供了 username，进行模糊匹配
    if username:
        query = query.filter(
            user_id__username__icontains=username
        )
    # 获取总数量
    total = await query.count()
    # 分页查询
    students = await query.offset((page - 1) * page_size).limit(page_size)

    data = []

    for student in students:
        profile = await User_Profiles.get_or_none(user_id=student.user_id.user_id)
        if not profile:
            continue

        data.append({
            "student_id": student.student_id,
            "fullname": profile.fullname,
            "gender": profile.gender,
            "enrollment_date":student.enrollment_date,
            "email": student.user_id.email,
            "license_type": student.license_type,
            "username": student.user_id.username,
            "phone": student.user_id.phone,
            "current_level": student.current_level,
            "birthday": profile.birthday,
            "address": profile.address,
            "user_id_id":student.user_id_id,
        })

    return total, data


async def get_student_by_user_id(user_id: int) -> Optional[Dict[str, Any]]:
    """
    根据用户ID获取学生信息
    :param user_id: 用户ID
    :return: 学生信息
    """
    student = await Student.get_or_none(user_id=user_id).select_related('user_id')
    if not student:
        return None

    profile = await User_Profiles.get_or_none(user_id=student.user_id.user_id)
    if not profile:
        return None

    return {
        "student_id": student.student_id,
        "fullname": profile.fullname,
        "username": student.user_id.username,
    }


async def get_student_by_id(student_id: int) -> Optional[Dict[str, Any]]:
    """
    根据学生ID获取学生信息
    :param student_id: 学生ID
    :return: 学生详细信息
    """
    student = await Student.get_or_none(student_id=student_id).select_related('user_id')
    if not student:
        return None

    profile = await User_Profiles.get_or_none(user_id=student.user_id.user_id)
    if not profile:
        return None

    return {
        "student_id": student.student_id,
        "password": student.user_id.password,
        "enrollment_date": student.enrollment_date,
        "fullname": profile.fullname,
        "email": student.user_id.email,
        "gender": profile.gender,
        "username": student.user_id.username,
        "phone": student.user_id.phone,
        "birthday": profile.birthday,
        "address": profile.address,
        "current_level": student.current_level,
        "license_type": student.license_type,
    }


async def create_student(data: StudentCreateRequest) -> bool:
    """
    创建新的学生
    :param data: 学生创建请求数据
    :return: 创建是否成功
    """
    # 检查用户名是否存在
    existing_user = await User.get_or_none(username=data.username)
    if existing_user:
        raise ValueError("Username already exists")

    try:
        async with in_transaction() as connection:
            # 创建 User
            user = await User.create(
                username=data.username,
                password=data.password,
                email=data.email,
                phone=data.phone,
                role="STUDENT",
                using_db=connection
            )

            # 创建 User_Profile - 传递user对象而不是user.user_id
            await User_Profiles.create(
                user_id=user,  # 这里改为传递user对象
                fullname=data.fullname,
                gender=data.gender,
                birthday=data.birthday,
                address=data.address,
                id_number=data.id_number,
                using_db=connection
            )
            # 如果 enrollment_date 未提供，则设置为当前时间
            enrollment_date = data.enrollment_date if data.enrollment_date is not None else datetime.now()

            # 创建 Student - 传递user对象而不是user.user_id
            await Student.create(
                user_id=user,  # 这里改为传递user对象
                enrollment_date=enrollment_date,  # 使用当前时间作为默认值
                current_level=data.current_level,
                license_type=data.license_type,
                using_db=connection
            )
        return True
    except Exception as e:
        raise Exception(f"Error creating student: {str(e)}")


async def update_student(student_id: int, data: StudentUpdateRequest) -> bool:
    """
    更新学生信息
    :param student_id: 学生ID
    :param data: 学生更新请求数据
    :return: 更新是否成功
    """
    # 查询 Student 并预加载关联的 User 和 User_Profiles
    student = await Student.get_or_none(student_id=student_id)
    if not student:
        return False

    # 加载关联的 User
    user = await User.get_or_none(user_id=student.user_id_id)
    if not user:
        return False

    # 加载关联的 User_Profiles
    profile = await User_Profiles.get_or_none(user_id=user.user_id)
    if not profile:
        return False

    update_data = data.dict(exclude_unset=True)  # 只获取提供的字段

    # 更新 User 表
    if any(k in update_data for k in ['username', 'password', 'email', 'phone']):
        if 'username' in update_data:
            # 检查新用户名是否已被占用（排除当前用户）
            existing_user = await User.get_or_none(username=update_data['username'])
            if existing_user and existing_user.user_id != user.user_id:
                raise ValueError("Username already exists")

        # 使用 update_from_dict 安全更新字段
        await user.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['username', 'password', 'email', 'phone']
        })

    # 更新 User_Profiles 表
    if any(k in update_data for k in ['fullname', 'gender', 'birthday', 'address']):
        await profile.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['fullname', 'gender', 'birthday', 'address']
        })

    # 更新 Student 表
    if any(k in update_data for k in ['enrollment_date', 'current_level', 'license_type']):
        await student.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['enrollment_date', 'current_level', 'license_type']
        })

    await user.save()
    await profile.save()
    await student.save()
    return True


async def delete_student(student_id: int) -> bool:
    """
    删除学生
    :param student_id: 学生ID
    :return: 删除是否成功
    """
    student = await Student.get_or_none(student_id=student_id)
    if not student:
        return False

    user_id = student.user_id_id
    # 删除关联的 User_Profiles
    await User_Profiles.filter(user_id=user_id).delete()
    # 删除关联的 User
    await User.filter(user_id=user_id).delete()

    await student.delete()
    return True
